package com.jdbc.dbutilstest;

import com.jdbc.entity.Dog;
import com.jdbc.entity.Type;
import com.jdbc.utils.JDBCUtils;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author wzm
 * @create 2023-04-11 9:36
 */
//演示DBUtils进行CRUD操作
public class DBUtilsTest {
    //新增
    @Test
    public void add() throws SQLException {
        //1.获取执行sql的对象
        QueryRunner runner = new QueryRunner();

        //获取连接
        Connection conn = JDBCUtils.getConn();

        //定义sql
        String sql = "insert into dog values(?,?,?,?)";

        //2.执行sql update(连接,sql语句，sql语句的问号参数)
        int update = runner.update(conn, sql, null, "塞班", 4, "公");
        System.out.println(" 当前添加记录数为： " + update);

        //DbUtils关闭资源
        DbUtils.closeQuietly(conn);

    }

    //删除
    @Test
    public void remove() throws SQLException {
        QueryRunner runner = new QueryRunner();

        Connection conn = JDBCUtils.getConn();
        String sql ="delete from  dog where id = ?";

        int update = runner.update(conn, sql, 6);
        System.out.println("update = " + update);
        DbUtils.closeQuietly(conn);
    }


    //修改
    @Test
    public void modify() throws SQLException {
        QueryRunner runner = new QueryRunner();

        Connection conn = JDBCUtils.getConn();
        String sql ="update dog set name = ?,age = ?,sex = ? where id = ?";

        int update = runner.update(conn, sql, "大黃狗",1,"母",7);
        System.out.println("update = " + update);
        DbUtils.closeQuietly(conn);
    }


    //查询单个对象  一条数据
    //根据id查询dog信息
    @Test
    public void selectById() throws SQLException {
        QueryRunner runner = new QueryRunner();
        Connection conn = JDBCUtils.getConn();
        String sql = "select * from dog where id = ?";

        //创建一个单个对象结果集的处理器
        BeanHandler<Dog> handler = new BeanHandler<>(Dog.class);

        Dog dog = runner.query(conn, sql, handler, 7);
        System.out.println("单个对象dog = " + dog);
    }



    //查询集合  所有数据
    @Test
    public void selectAll() throws SQLException {
        QueryRunner runner = new QueryRunner();
        Connection conn = JDBCUtils.getConn();
        String sql = "select * from dog";

        //查询多个对象返回集合的处理器
        BeanListHandler<Dog> handler = new BeanListHandler<>(Dog.class);

        List<Dog> query = runner.query(conn, sql, handler);
        System.out.println("query = " + query);
    }


    //查询单个值  max min avg count sum
    @Test
    public void selectByValue() throws SQLException {
        QueryRunner runner = new QueryRunner();
        Connection conn = JDBCUtils.getConn();
        String sql = "select count(*) from dog where id < ?";

        //创建查询单值的结果处理器
        ScalarHandler handler = new ScalarHandler();

        Object o = runner.query(conn, sql, handler, 7);

        //Object = > int
        Number number = (Number) o;
        int i = number.intValue();
        System.out.println("i = " + i);
        DbUtils.closeQuietly(conn);

    }


    //查询Dog的id=1的信息和它所属的类型名称
    @Test
    public void selectByIdAndType() throws SQLException {
        QueryRunner runner = new QueryRunner();
        Connection conn = JDBCUtils.getConn();
        String sql = "select d.*,t.typename\n" +
                "from dog d\n" +
                "left join type t\n" +
                "on d.typeid = t.typeid\n" +
                "where id = ?;\n";

        //BeanHandler<Dog> handler = new BeanHandler<>(Dog.class);
        //根据查询到的列名给实体的属性赋值

        //采用自定义的处理器
        DogHandler<Dog> handler = new DogHandler<>();


        Dog dog = runner.query(conn, sql, handler, 1);

        System.out.println("dog = " + dog);
        System.out.println("名字："+dog.getName()+"类别为："+dog.getType().getTypename());
    }

    //匿名实现类 通常情况下 我们写一个类来实现接口  实现抽象方法
    //匿名实现类就是这个类不需要定义出来直接写


    @Test
    public void selectByIdAndType01() throws SQLException {
        QueryRunner runner = new QueryRunner();

        Connection conn = JDBCUtils.getConn();

        String sql = "select d.*,t.typename\n" +
                "from dog d\n" +
                "left join type t\n" +
                "on d.typeid = t.typeid\n" +
                "where id = ?;\n";

        //采用匿名实现类作为处理器
        ResultSetHandler<Dog> handler = new ResultSetHandler<Dog>() {
            //实现处理器的方法
            @Override
            public Dog handle(ResultSet rs) throws SQLException {
                //手动处理查询到的结果
                Dog dog = null;
                if(rs.next()){
                    dog =  new Dog();
                    //查询到的dog的信息 赋值
                    dog.setId(rs.getInt(1));
                    dog.setName(rs.getString(2));
                    dog.setAge(rs.getInt(3));
                    dog.setSex(rs.getString(4));

                    //类型相关的东西
                    Type type = new Type();
                    type.setTypeid(rs.getInt(5));
                    type.setTypename(rs.getString(6));
                    //将type对象赋值给dog的type属性
                    dog.setType(type);
                }
                return dog;
            }
        };


        Dog dog = runner.query(conn, sql, handler, 1);
        System.out.println("dog = " + dog);
        System.out.println("名字："+dog.getName()+"类别为："+dog.getType().getTypename());
    }


}
